<!DOCTYPE html><html><head><title>MySQL学习笔记（Day012：子查询/INSERT/UPDATE/DELETE/REPLACE）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'><style>

.note-content  {font-family: 宋体, 'Helvetica Neue', Arial, 'Hiragino Sans GB', STHeiti, 'Microsoft YaHei', 'WenQuanYi Micro Hei', SimSun, Song, sans-serif;}
</style></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-1" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-2" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day012子查询insertupdatedeletereplace">MySQL学习笔记（Day012：子查询/INSERT/UPDATE/DELETE/REPLACE）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day012子查询insertupdatedeletereplace">MySQL学习笔记（Day012：子查询/INSERT/UPDATE/DELETE/REPLACE）</a><ul>
<li><a href="#一-子查询">一. 子查询</a><ul>
<li><a href="#1-子查询的使用">1. 子查询的使用</a><ul>
<li><a href="#11-any-some">1.1.  ANY / SOME</a></li>
<li><a href="#12-in">1.2. IN</a></li>
<li><a href="#13-all">1.3. ALL</a></li>
</ul>
</li>
<li><a href="#2-子查询的分类">2. 子查询的分类</a></li>
<li><a href="#3-子查询的优化">3. 子查询的优化</a></li>
<li><a href="#4-包含null值的not-in">4. 包含NULL值的NOT IN</a></li>
</ul>
</li>
<li><a href="#二-insert">二. INSERT</a></li>
<li><a href="#三-delete">三. DELETE</a></li>
<li><a href="#四-update">四. UPDATE</a></li>
<li><a href="#五-replace">五. REPLACE</a></li>
<li><a href="#六-其他知识点">六. 其他知识点</a></li>
</ul>
</li>
</ul>
</div>
</div>
</div>

</div><div id="wmd-preview-section-3" class="wmd-preview-section preview-content">

<h2 id="一-子查询">一. 子查询</h2>

<blockquote>
  <p>子查询就是指在一个select语句中嵌套另一个select语句。同时，子查询必须包含括号。 <br>
  <code>MySQL 5.6.x</code> 版本之前，MySQL的子查询性能较差，但是从5.6开始，不存在性能差的问题。</p>
</blockquote>

</div><div id="wmd-preview-section-4" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-operator"><span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t1 <span class="hljs-keyword">where</span> a &gt; <span class="hljs-keyword">any</span>(<span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t2);</span><br></code></pre>

<ol><li rel="1"><code>select a from t1</code> 是外部查询(outer query)</li>
<li rel="2"><code>(select a from t2)</code> 是子查询</li>
</ol>

<p>一般说来，子查询嵌套于外部查询中，可以将两个或两个以上的子查询进行嵌套</p>

</div><div id="wmd-preview-section-5" class="wmd-preview-section preview-content">

<h3 id="1-子查询的使用">1. 子查询的使用</h3>

</div><div id="wmd-preview-section-6" class="wmd-preview-section preview-content">

<h4 id="11-any-some">1.1.  ANY / SOME</h4>

<p>如果外部查询的列的结果和子查询的列的结果比较得到为True的话，则返回比较值为True的（外查询）的记录</p>

</div><div id="wmd-preview-section-7" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span>  t1 (a <span class="hljs-built_in">int</span>);</span><br><span class="hljs-comment line-number">2.</span>Query OK, 0 rows affected (0.15 sec)<br><span class="hljs-comment line-number">3.</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span>  t2 (a <span class="hljs-built_in">int</span>);</span> <br><span class="hljs-comment line-number">5.</span>Query OK, 0 rows affected (0.14 sec)<br><span class="hljs-comment line-number">6.</span><br><span class="hljs-comment line-number">7.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t1 <span class="hljs-keyword">values</span>(<span class="hljs-number">10</span>),(<span class="hljs-number">4</span>);</span><br><span class="hljs-comment line-number">8.</span>Query OK, 2 rows affected (0.02 sec)<br><span class="hljs-comment line-number">9.</span>Records: 2  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">10.</span><br><span class="hljs-comment line-number">11.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t2 <span class="hljs-keyword">values</span>(<span class="hljs-number">12</span>),(<span class="hljs-number">13</span>),(<span class="hljs-number">5</span>);</span><br><span class="hljs-comment line-number">12.</span>Query OK, 3 rows affected (0.03 sec)<br><span class="hljs-comment line-number">13.</span>Records: 3  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">14.</span><br><span class="hljs-comment line-number">15.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t1;</span><br><span class="hljs-comment line-number">16.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">17.</span>| a    |<br><span class="hljs-comment line-number">18.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">19.</span>|   10 |<br><span class="hljs-comment line-number">20.</span>|    4 |<br><span class="hljs-comment line-number">21.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">22.</span>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">23.</span><br><span class="hljs-comment line-number">24.</span>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t2;</span><br><span class="hljs-comment line-number">25.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">26.</span>| a    |<br><span class="hljs-comment line-number">27.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">28.</span>|   12 |   <span class="hljs-comment">-- t1中10，4比12小</span><br><span class="hljs-comment line-number">29.</span>|   13 |   <span class="hljs-comment">-- t1中10，4比13小</span><br><span class="hljs-comment line-number">30.</span>|    5 |   <span class="hljs-comment">-- t1中10比5大，4比5小</span><br><span class="hljs-comment line-number">31.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">32.</span>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">33.</span><br><span class="hljs-comment line-number">34.</span>mysql&gt; <span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t1  <br><span class="hljs-comment line-number">35.</span>    -&gt; <span class="hljs-keyword">where</span> a &gt; <span class="hljs-keyword">any</span><br><span class="hljs-comment line-number">36.</span>    -&gt; (<span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t2);</span> <span class="hljs-comment">-- 返回(12，13，4)</span><br><span class="hljs-comment line-number">37.</span>                           <span class="hljs-comment">-- t1中a列的值，只要大于(12,13,4)中任意一值</span><br><span class="hljs-comment line-number">38.</span>                           <span class="hljs-comment">-- 即t1.a &gt; t2.a为True，则返回对应的t1.a</span><br><span class="hljs-comment line-number">39.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">40.</span>| a    |<br><span class="hljs-comment line-number">41.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">42.</span>|   10 |  <span class="hljs-comment">-- 10 比 5 大为True，则返回该值，4比t2中所有的a值小，为False</span><br><span class="hljs-comment line-number">43.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">44.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">45.</span><br><span class="hljs-comment line-number">46.</span><span class="hljs-comment">-- 这个查询可以解释为，t1表内a列的值 大于 t2表中a列的任意(any)一个值（t1.a &gt; any(t2.a) == true）,则返回t1.a的记录</span></span><br></code></pre>

<blockquote>
  <p><code>ANY</code>关键词必须与一个<code>比较操作符</code>一起使用： <code>=</code>, <code>&gt;</code>, <code>&lt;</code>, <code>&gt;=</code>, <code>&lt;=</code>, <code>&lt;&gt;</code> <em>(这个是!=的意思)</em></p>
  
  <p><strong>子查询中<code>SOME</code>和<code>ANY</code>是同一个意思</strong></p>
</blockquote>

</div><div id="wmd-preview-section-8" class="wmd-preview-section preview-content">

<h4 id="12-in">1.2. IN</h4>

<p><code>in</code>是<code>ANY</code>的一种特殊情况：<strong><code>"in"</code></strong>  <code>equals</code>  <strong><code>"= any"</code></strong></p>

</div><div id="wmd-preview-section-9" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t1 <span class="hljs-keyword">values</span>(<span class="hljs-number">5</span>);</span>  <span class="hljs-comment">-- 向t1中插入一个t2中存在的值 5</span><br><span class="hljs-comment line-number">2.</span>Query OK, 1 row affected (0.03 sec)<br><span class="hljs-comment line-number">3.</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t1 <span class="hljs-keyword">where</span> a = <span class="hljs-keyword">any</span>(<span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t2);</span> <span class="hljs-comment">-- t1.a==t2.a 的只有5</span><br><span class="hljs-comment line-number">5.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">6.</span>| a    |<br><span class="hljs-comment line-number">7.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">8.</span>|    5 |<br><span class="hljs-comment line-number">9.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">10.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">11.</span><br><span class="hljs-comment line-number">12.</span>mysql&gt; <span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t1 <span class="hljs-keyword">where</span> a <span class="hljs-keyword">in</span> (<span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t2);</span> <span class="hljs-comment">-- in的结果等同于 =any 的结果</span><br><span class="hljs-comment line-number">13.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">14.</span>| a    |<br><span class="hljs-comment line-number">15.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">16.</span>|    5 |<br><span class="hljs-comment line-number">17.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">18.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p><code>select a from s1 where  a in (select a in t2);</code>是用的比较多的一种语法</p>
</blockquote>

</div><div id="wmd-preview-section-10" class="wmd-preview-section preview-content">

<h4 id="13-all">1.3. ALL</h4>

<p>如果外部查询的列的结果和子查询的列的<code>所有结果</code>比较得到为True的话，则返回比较值为True的（外查询）的记录</p>

</div><div id="wmd-preview-section-11" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">truncate</span> t1;</span>   <span class="hljs-comment">-- 清空t1</span><br><span class="hljs-comment line-number">2.</span>Query OK, 0 rows affected (0.07 sec)<br><span class="hljs-comment line-number">3.</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">truncate</span> t2;</span>   <span class="hljs-comment">-- 清空t2</span><br><span class="hljs-comment line-number">5.</span>Query OK, 0 rows affected (0.10 sec)<br><span class="hljs-comment line-number">6.</span><br><span class="hljs-comment line-number">7.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t1 <span class="hljs-keyword">values</span>(<span class="hljs-number">10</span>),(<span class="hljs-number">4</span>);</span><br><span class="hljs-comment line-number">8.</span>Query OK, 2 rows affected (0.02 sec)<br><span class="hljs-comment line-number">9.</span>Records: 2  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">10.</span><br><span class="hljs-comment line-number">11.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t2 <span class="hljs-keyword">values</span>(<span class="hljs-number">5</span>),(<span class="hljs-number">4</span>),(<span class="hljs-number">3</span>);</span>  <br><span class="hljs-comment line-number">12.</span>Query OK, 3 rows affected (0.03 sec)<br><span class="hljs-comment line-number">13.</span>Records: 3  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">14.</span><br><span class="hljs-comment line-number">15.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t1 <span class="hljs-keyword">where</span> a &gt; all(<span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t2);</span><br><span class="hljs-comment line-number">16.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">17.</span>| a    |<br><span class="hljs-comment line-number">18.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">19.</span>|   10 |  <span class="hljs-comment">-- (10 &gt; 5, 4, 3 为 True) 而 (4 &gt;5, 4, 3 为 False)</span><br><span class="hljs-comment line-number">20.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">21.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p><code>ALL</code>关键词必须与一个<code>比较操作符</code>一起使用 <br>
  <code>NOT IN</code> 是 <code>&lt;&gt; ALL</code>的别名 </p>
</blockquote>

</div><div id="wmd-preview-section-12" class="wmd-preview-section preview-content">

<h3 id="2-子查询的分类">2. 子查询的分类</h3>

<ul><li><p><strong>独立子查询</strong></p>

<ul>
<li>不依赖外部查询而运行的子查询 <br>
<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t1 <span class="hljs-keyword">where</span> a <span class="hljs-keyword">in</span> (<span class="hljs-number">1</span>,<span class="hljs-number">2</span>,<span class="hljs-number">3</span>,<span class="hljs-number">4</span>,<span class="hljs-number">5</span>);</span><br><span class="hljs-comment line-number">2.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">3.</span>| a    |<br><span class="hljs-comment line-number">4.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">5.</span>|    4 |  <br><span class="hljs-comment line-number">6.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">7.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre></li></ul></li>
<li><p><strong>相关子查询</strong></p>

<ul>
<li>引用了外部查询列的子查询 <br>
<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">-- 在这个例子中，子查询中使用到了外部的列t2.a </span><br><span class="hljs-comment line-number">2.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t1 <span class="hljs-keyword">where</span> a <span class="hljs-keyword">in</span> (<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t2 <span class="hljs-keyword">where</span> t1.a = t2.a);</span><br><span class="hljs-comment line-number">3.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">4.</span>| a    |<br><span class="hljs-comment line-number">5.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">6.</span>|    4 |<br><span class="hljs-comment line-number">7.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">8.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre></li></ul></li>
</ul>

</div><div id="wmd-preview-section-13" class="wmd-preview-section preview-content">

<h3 id="3-子查询的优化">3. 子查询的优化</h3>

<ul><li><strong>MySQL5.6之前</strong> <br>
在<code>MySQL5.6</code>之前，优化器会把子查询重写成<code>exists</code>的形式</li>
</ul>

</div><div id="wmd-preview-section-14" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-operator"><span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t1 <span class="hljs-keyword">where</span> a <span class="hljs-keyword">in</span> (<span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t2);</span> <span class="hljs-comment">-- 这个是一条独立的子查询，时间复杂度 O(M+N)</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">3.</span><span class="hljs-comment">-- 经过优化器重写后</span><br><span class="hljs-comment line-number">4.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">5.</span><span class="hljs-operator"><span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t1 <span class="hljs-keyword">where</span> <span class="hljs-keyword">exists</span> (<span class="hljs-keyword">select</span> <span class="hljs-number">1</span> <span class="hljs-keyword">from</span> t2 <span class="hljs-keyword">where</span> t1.a = t2.a);</span> <span class="hljs-comment">-- 这是相关子查询，复杂度O(M*N + M)</span><br></code></pre>

<p>所以在<code>MySQL 5.6</code>之前，部分的子查询需要重写成join的形式 <em>（注意表的大小）</em></p>

</div><div id="wmd-preview-section-15" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> t1.a <span class="hljs-keyword">from</span> t1 <span class="hljs-keyword">join</span> t2 <span class="hljs-keyword">on</span> t1.a = t2.a;</span><br><span class="hljs-comment line-number">2.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">3.</span>| a    |<br><span class="hljs-comment line-number">4.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">5.</span>|    4 |<br><span class="hljs-comment line-number">6.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">7.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<ul><li><strong>MySQL 5.6之后</strong> <br>
在<code>MySQL 5.6</code>之后，优化器<code>不会</code>将子查询<code>重写</code>成<code>exists</code>的形式，而是自动优化，性能有了大幅提升</li>
</ul>

<blockquote>
  <p>可通过<code>explain extended</code>来查看子查询优化的结果。由于<code>explain</code>还未讲到，该部分暂时跳过</p>
</blockquote>

</div><div id="wmd-preview-section-16" class="wmd-preview-section preview-content">

<h3 id="4-包含null值的not-in">4. 包含NULL值的NOT IN</h3>

</div><div id="wmd-preview-section-17" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-literal">null</span> <span class="hljs-keyword">in</span> (<span class="hljs-string">'a'</span>, <span class="hljs-string">'b'</span>, <span class="hljs-literal">null</span>);</span><br><span class="hljs-comment line-number">2.</span>+<span class="hljs-comment">--------------------------+</span><br><span class="hljs-comment line-number">3.</span>| null in ('a', 'b', null) |<br><span class="hljs-comment line-number">4.</span>+<span class="hljs-comment">--------------------------+</span><br><span class="hljs-comment line-number">5.</span>|                     NULL |<br><span class="hljs-comment line-number">6.</span>+<span class="hljs-comment">--------------------------+</span><br><span class="hljs-comment line-number">7.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p>MySQL数据库的<code>比较操作</code>，除了返回<code>1(True)</code>, <code>0(False)</code>之外，还会返回<code>NULL</code> <br>
  <code>NULL</code>和<code>NULL</code>的比较，返回的还是<code>NULL</code></p>
</blockquote>

</div><div id="wmd-preview-section-18" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-literal">null</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span> (<span class="hljs-string">'a'</span>, <span class="hljs-string">'b'</span>, <span class="hljs-literal">null</span>);</span>  <br><span class="hljs-comment line-number">2.</span>+<span class="hljs-comment">------------------------------+</span><br><span class="hljs-comment line-number">3.</span>| null not in ('a', 'b', null) |<br><span class="hljs-comment line-number">4.</span>+<span class="hljs-comment">------------------------------+</span><br><span class="hljs-comment line-number">5.</span>|                         NULL |  <span class="hljs-comment">-- null不在('a', 'b', null)中，返回的还是null，因为有null和null的比较</span><br><span class="hljs-comment line-number">6.</span>+<span class="hljs-comment">------------------------------+</span><br><span class="hljs-comment line-number">7.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">8.</span><br><span class="hljs-comment line-number">9.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-string">'a'</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span> (<span class="hljs-string">'a'</span>, <span class="hljs-string">'b'</span>, <span class="hljs-literal">null</span>);</span>  <br><span class="hljs-comment line-number">10.</span>+<span class="hljs-comment">-----------------------------+</span><br><span class="hljs-comment line-number">11.</span>| 'a' not in ('a', 'b', null) |<br><span class="hljs-comment line-number">12.</span>+<span class="hljs-comment">-----------------------------+</span><br><span class="hljs-comment line-number">13.</span>|                           0 |  <span class="hljs-comment">-- a 不在 ('a', 'b', null)中，返回0,即False</span><br><span class="hljs-comment line-number">14.</span>+<span class="hljs-comment">-----------------------------+</span><br><span class="hljs-comment line-number">15.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">16.</span><br><span class="hljs-comment line-number">17.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-string">'c'</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span> (<span class="hljs-string">'a'</span>, <span class="hljs-string">'b'</span>);</span><br><span class="hljs-comment line-number">18.</span>+<span class="hljs-comment">-----------------------+</span><br><span class="hljs-comment line-number">19.</span>| 'c' not in ('a', 'b') |<br><span class="hljs-comment line-number">20.</span>+<span class="hljs-comment">-----------------------+</span><br><span class="hljs-comment line-number">21.</span>|                     1 |  <span class="hljs-comment">-- 这个返回值可以理解 'c'不在('a', 'b')中，返回1，即为True</span><br><span class="hljs-comment line-number">22.</span>+<span class="hljs-comment">-----------------------+</span><br><span class="hljs-comment line-number">23.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">24.</span><br><span class="hljs-comment line-number">25.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-string">'c'</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span> (<span class="hljs-string">'a'</span>, <span class="hljs-string">'b'</span>, <span class="hljs-literal">null</span>);</span> <br><span class="hljs-comment line-number">26.</span>+<span class="hljs-comment">-----------------------------+</span><br><span class="hljs-comment line-number">27.</span>| 'c' not in ('a', 'b', null) |<br><span class="hljs-comment line-number">28.</span>+<span class="hljs-comment">-----------------------------+</span><br><span class="hljs-comment line-number">29.</span>|                        NULL | <span class="hljs-comment">-- 理论上应该是返回1，即True的。但是包含了null值。则返回null</span><br><span class="hljs-comment line-number">30.</span>+<span class="hljs-comment">-----------------------------+</span><br><span class="hljs-comment line-number">31.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p><strong>对于包含了<code>NULL</code>值的<code>IN</code>操作，总是返回<code>True</code>或者<code>NULL</code></strong> <br>
  <strong><code>NOT IN</code>返回<code>NOT True (False)</code>或者<code>NOT NULL (NULL)</code></strong></p>
</blockquote>

</div><div id="wmd-preview-section-19" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-comment">-- SQL语句一 使用 EXISTS</span><br><span class="hljs-comment line-number">3.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">4.</span><span class="hljs-operator"><span class="hljs-keyword">select</span> customerid, companyname <br><span class="hljs-comment line-number">5.</span>    <span class="hljs-keyword">from</span> customers <span class="hljs-keyword">as</span> A<br><span class="hljs-comment line-number">6.</span>    <span class="hljs-keyword">where</span> country = <span class="hljs-string">'Spain'</span> <br><span class="hljs-comment line-number">7.</span>        <span class="hljs-keyword">and</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span><br><span class="hljs-comment line-number">8.</span>            ( <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> orders <span class="hljs-keyword">as</span> B<br><span class="hljs-comment line-number">9.</span>              <span class="hljs-keyword">where</span> A.customerid = B.customerid );</span><br><span class="hljs-comment line-number">10.</span><br><span class="hljs-comment line-number">11.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">12.</span><span class="hljs-comment">-- SQL语句二 使用 IN</span><br><span class="hljs-comment line-number">13.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">14.</span><span class="hljs-operator"><span class="hljs-keyword">select</span> customerid, companyname <br><span class="hljs-comment line-number">15.</span>    <span class="hljs-keyword">from</span> customers <span class="hljs-keyword">as</span> A<br><span class="hljs-comment line-number">16.</span>    <span class="hljs-keyword">where</span> country = <span class="hljs-string">'Spain'</span> <br><span class="hljs-comment line-number">17.</span>        <span class="hljs-keyword">and</span> customerid <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span> (<span class="hljs-keyword">select</span> customerid <span class="hljs-keyword">from</span> orders);</span><br><span class="hljs-comment line-number">18.</span><br><span class="hljs-comment line-number">19.</span><span class="hljs-comment">-----</span><br><span class="hljs-comment line-number">20.</span><span class="hljs-comment">-- 当结果集合中没有NULL值时，上述两条SQL语句查询的结果是一致的 </span><br><span class="hljs-comment line-number">21.</span><span class="hljs-comment">-----</span><br><span class="hljs-comment line-number">22.</span><br><span class="hljs-comment line-number">23.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">24.</span><span class="hljs-comment">-- 插入一个NULL值</span><br><span class="hljs-comment line-number">25.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">26.</span><span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> orders(orderid) <span class="hljs-keyword">values</span> (<span class="hljs-literal">null</span>);</span><br><span class="hljs-comment line-number">27.</span><br><span class="hljs-comment line-number">28.</span><span class="hljs-comment">-----</span><br><span class="hljs-comment line-number">29.</span><span class="hljs-comment">-- SQL语句1 : 返回和之前一致</span><br><span class="hljs-comment line-number">30.</span><span class="hljs-comment">-- SQL语句2 : 返回为空表，因为子查询返回的结果集中存在NULL值。not in null 永远返回False或者NULL</span><br><span class="hljs-comment line-number">31.</span><span class="hljs-comment">--            此时 where (country = 'Spain' and (False or NULL)) 为 False OR NULL，条件永远不匹配</span><br><span class="hljs-comment line-number">32.</span><span class="hljs-comment">-----</span><br><span class="hljs-comment line-number">33.</span><br><span class="hljs-comment line-number">34.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">35.</span><span class="hljs-comment">-- SQL语句2 改写后</span><br><span class="hljs-comment line-number">36.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">37.</span><span class="hljs-operator"><span class="hljs-keyword">select</span> customerid, companyname <br><span class="hljs-comment line-number">38.</span>    <span class="hljs-keyword">from</span> customers <span class="hljs-keyword">as</span> A<br><span class="hljs-comment line-number">39.</span>    <span class="hljs-keyword">where</span> country = <span class="hljs-string">'Spain'</span> <br><span class="hljs-comment line-number">40.</span>        <span class="hljs-keyword">and</span> customerid <span class="hljs-keyword">not</span> <span class="hljs-keyword">in</span> (<span class="hljs-keyword">select</span> customerid <span class="hljs-keyword">from</span> orders <br><span class="hljs-comment line-number">41.</span>                                <span class="hljs-keyword">where</span> customerid <span class="hljs-keyword">is</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>);</span>  <span class="hljs-comment">-- 增加这个过滤条件，使用is not，而不是&lt;&gt;</span><br><span class="hljs-comment line-number">42.</span><br><span class="hljs-comment line-number">43.</span><br><span class="hljs-comment line-number">44.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">45.</span><span class="hljs-comment">-- 和 null比较，使用is和is not， 而不是 = 和 &lt;&gt;</span><br><span class="hljs-comment line-number">46.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">47.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-literal">null</span> = <span class="hljs-literal">null</span>;</span> <br><span class="hljs-comment line-number">48.</span>+<span class="hljs-comment">-------------+</span><br><span class="hljs-comment line-number">49.</span>| null = null |<br><span class="hljs-comment line-number">50.</span>+<span class="hljs-comment">-------------+</span><br><span class="hljs-comment line-number">51.</span>|        NULL |<br><span class="hljs-comment line-number">52.</span>+<span class="hljs-comment">-------------+</span><br><span class="hljs-comment line-number">53.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">54.</span><br><span class="hljs-comment line-number">55.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-literal">null</span> &lt;&gt; <span class="hljs-literal">null</span>;</span><br><span class="hljs-comment line-number">56.</span>+<span class="hljs-comment">--------------+</span><br><span class="hljs-comment line-number">57.</span>| null &lt;&gt; null |<br><span class="hljs-comment line-number">58.</span>+<span class="hljs-comment">--------------+</span><br><span class="hljs-comment line-number">59.</span>|         NULL |<br><span class="hljs-comment line-number">60.</span>+<span class="hljs-comment">--------------+</span><br><span class="hljs-comment line-number">61.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">62.</span><br><span class="hljs-comment line-number">63.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-literal">null</span> <span class="hljs-keyword">is</span> <span class="hljs-literal">null</span>;</span> <br><span class="hljs-comment line-number">64.</span>+<span class="hljs-comment">--------------+</span><br><span class="hljs-comment line-number">65.</span>| null is null |<br><span class="hljs-comment line-number">66.</span>+<span class="hljs-comment">--------------+</span><br><span class="hljs-comment line-number">67.</span>|            1 |  <span class="hljs-comment">-- 返回 True</span><br><span class="hljs-comment line-number">68.</span>+<span class="hljs-comment">--------------+</span><br><span class="hljs-comment line-number">69.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">70.</span><br><span class="hljs-comment line-number">71.</span>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-literal">null</span> <span class="hljs-keyword">is</span> <span class="hljs-keyword">not</span>  <span class="hljs-literal">null</span>;</span><br><span class="hljs-comment line-number">72.</span>+<span class="hljs-comment">-------------------+</span><br><span class="hljs-comment line-number">73.</span>| null is not  null |<br><span class="hljs-comment line-number">74.</span>+<span class="hljs-comment">-------------------+</span><br><span class="hljs-comment line-number">75.</span>|                 0 |  <span class="hljs-comment">-- 返回 False</span><br><span class="hljs-comment line-number">76.</span>+<span class="hljs-comment">-------------------+</span><br><span class="hljs-comment line-number">77.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p><code>EXISTS</code>不管返回值是什么，而是看是否有<code>行</code>返回，所以<code>EXISTS</code>中子查询都是<code>select *</code>、<code>select 1</code>等，因为只关心返回是否有行（结果集）</p>
</blockquote>

<hr>

</div><div id="wmd-preview-section-20" class="wmd-preview-section preview-content">

<h2 id="二-insert">二. INSERT</h2>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/insert.html" target="_blank">官方文档</a></p>
</blockquote>

</div><div id="wmd-preview-section-21" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t1 <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>);</span>  <span class="hljs-comment">-- 插入一个值</span><br><span class="hljs-comment line-number">2.</span>Query OK, 1 row affected (0.03 sec)<br><span class="hljs-comment line-number">3.</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t1 <span class="hljs-keyword">values</span>(<span class="hljs-number">2</span>),(<span class="hljs-number">3</span>),(-<span class="hljs-number">1</span>);</span>  <span class="hljs-comment">-- 插入多个值，MySQL独有</span><br><span class="hljs-comment line-number">5.</span>Query OK, 3 rows affected (0.03 sec)<br><span class="hljs-comment line-number">6.</span>Records: 3  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">7.</span><br><span class="hljs-comment line-number">8.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t1 <span class="hljs-keyword">select</span> <span class="hljs-number">8</span>;</span>   <span class="hljs-comment">-- insert XXX select XXX 语法，MySQ独有</span><br><span class="hljs-comment line-number">9.</span>Query OK, 1 row affected (0.02 sec)<br><span class="hljs-comment line-number">10.</span>Records: 1  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">11.</span><br><span class="hljs-comment line-number">12.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> t3 (a <span class="hljs-built_in">int</span>, b <span class="hljs-built_in">int</span>);</span> <span class="hljs-comment">-- 有多个列</span><br><span class="hljs-comment line-number">13.</span>Query OK, 0 rows affected (0.15 sec)<br><span class="hljs-comment line-number">14.</span><br><span class="hljs-comment line-number">15.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t3 <span class="hljs-keyword">select</span> <span class="hljs-number">8</span>;</span>  <span class="hljs-comment">-- 没有指定列，报错</span><br><span class="hljs-comment line-number">16.</span>ERROR 1136 (21S01): Column count doesn't match value count at row 1<br><span class="hljs-comment line-number">17.</span><br><span class="hljs-comment line-number">18.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t3(a) <span class="hljs-keyword">select</span> <span class="hljs-number">8</span>;</span>  <span class="hljs-comment">-- 指定列a</span><br><span class="hljs-comment line-number">19.</span>Query OK, 1 row affected (0.04 sec)<br><span class="hljs-comment line-number">20.</span>Records: 1  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">21.</span><br><span class="hljs-comment line-number">22.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t3 <span class="hljs-keyword">select</span> <span class="hljs-number">8</span>, <span class="hljs-number">9</span>;</span>  <span class="hljs-comment">-- 不指定列，但是插入值匹配列的个数和类型</span><br><span class="hljs-comment line-number">23.</span>Query OK, 1 row affected (0.03 sec)<br><span class="hljs-comment line-number">24.</span>Records: 1  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">25.</span><br><span class="hljs-comment line-number">26.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t3;</span><br><span class="hljs-comment line-number">27.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">28.</span>| a    | b    |<br><span class="hljs-comment line-number">29.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">30.</span>|    8 | NULL |<br><span class="hljs-comment line-number">31.</span>|    8 |    9 |<br><span class="hljs-comment line-number">32.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">33.</span>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">34.</span><br><span class="hljs-comment line-number">35.</span>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t3(b) <span class="hljs-keyword">select</span> a <span class="hljs-keyword">from</span> t2;</span>  <span class="hljs-comment">-- 从t2表中查询数据并插入到t3(a)中，注意指定列</span><br><span class="hljs-comment line-number">36.</span>Query OK, 3 rows affected (0.03 sec)<br><span class="hljs-comment line-number">37.</span>Records: 3  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">38.</span><br><span class="hljs-comment line-number">39.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t3;</span><br><span class="hljs-comment line-number">40.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">41.</span>| a    | b    |<br><span class="hljs-comment line-number">42.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">43.</span>|    8 | NULL |<br><span class="hljs-comment line-number">44.</span>|    8 |    9 |<br><span class="hljs-comment line-number">45.</span>| NULL |    5 |<br><span class="hljs-comment line-number">46.</span>| NULL |    4 |<br><span class="hljs-comment line-number">47.</span>| NULL |    3 |<br><span class="hljs-comment line-number">48.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">49.</span>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">50.</span><br><span class="hljs-comment line-number">51.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">52.</span><span class="hljs-comment">-- 如果想快速增长表格中的数据，可以使用如下方法，使得数据成倍增长</span><br><span class="hljs-comment line-number">53.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">54.</span>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t3 <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t3;</span><br><span class="hljs-comment line-number">55.</span>Query OK, 5 rows affected (0.03 sec)  <span class="hljs-comment">-- 插入了5列</span><br><span class="hljs-comment line-number">56.</span>Records: 5  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">57.</span><br><span class="hljs-comment line-number">58.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t3;</span>               <br><span class="hljs-comment line-number">59.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">60.</span>| a    | b    |<br><span class="hljs-comment line-number">61.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">62.</span>|    8 | NULL |<br><span class="hljs-comment line-number">63.</span>|    8 |    9 |<br><span class="hljs-comment line-number">64.</span>| NULL |    5 |<br><span class="hljs-comment line-number">65.</span>| NULL |    4 |<br><span class="hljs-comment line-number">66.</span>| NULL |    3 |<br><span class="hljs-comment line-number">67.</span>|    8 | NULL |<br><span class="hljs-comment line-number">68.</span>|    8 |    9 |<br><span class="hljs-comment line-number">69.</span>| NULL |    5 |<br><span class="hljs-comment line-number">70.</span>| NULL |    4 |<br><span class="hljs-comment line-number">71.</span>| NULL |    3 |<br><span class="hljs-comment line-number">72.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">73.</span>10 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">74.</span><br><span class="hljs-comment line-number">75.</span>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t3 <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t3;</span><br><span class="hljs-comment line-number">76.</span>Query OK, 10 rows affected (0.03 sec)  <span class="hljs-comment">-- 插入了10列，成倍增长</span><br><span class="hljs-comment line-number">77.</span>Records: 10  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">78.</span><br><span class="hljs-comment line-number">79.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t3;</span>               <br><span class="hljs-comment line-number">80.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">81.</span>| a    | b    |<br><span class="hljs-comment line-number">82.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">83.</span>|    8 | NULL |<br><span class="hljs-comment line-number">84.</span>|    8 |    9 |<br><span class="hljs-comment line-number">85.</span>| NULL |    5 |<br><span class="hljs-comment line-number">86.</span>| NULL |    4 |<br><span class="hljs-comment line-number">87.</span>| NULL |    3 |<br><span class="hljs-comment line-number">88.</span>|    8 | NULL |<br><span class="hljs-comment line-number">89.</span>|    8 |    9 |<br><span class="hljs-comment line-number">90.</span>| NULL |    5 |<br><span class="hljs-comment line-number">91.</span>| NULL |    4 |<br><span class="hljs-comment line-number">92.</span>| NULL |    3 |<br><span class="hljs-comment line-number">93.</span>|    8 | NULL |<br><span class="hljs-comment line-number">94.</span>|    8 |    9 |<br><span class="hljs-comment line-number">95.</span>| NULL |    5 |<br><span class="hljs-comment line-number">96.</span>| NULL |    4 |<br><span class="hljs-comment line-number">97.</span>| NULL |    3 |<br><span class="hljs-comment line-number">98.</span>|    8 | NULL |<br><span class="hljs-comment line-number">99.</span>|    8 |    9 |<br><span class="hljs-comment line-number">100.</span>| NULL |    5 |<br><span class="hljs-comment line-number">101.</span>| NULL |    4 |<br><span class="hljs-comment line-number">102.</span>| NULL |    3 |<br><span class="hljs-comment line-number">103.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">104.</span>20 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<hr>

</div><div id="wmd-preview-section-22" class="wmd-preview-section preview-content">

<h2 id="三-delete">三. DELETE</h2>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/delete.html" target="_blank">官方文档</a></p>
</blockquote>

</div><div id="wmd-preview-section-23" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> t3 <span class="hljs-keyword">where</span> a <span class="hljs-keyword">is</span> <span class="hljs-literal">null</span>;</span>  <span class="hljs-comment">-- 根据过滤条件删除</span><br><span class="hljs-comment line-number">2.</span>Query OK, 12 rows affected (0.03 sec)<br><span class="hljs-comment line-number">3.</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t3;</span>               <br><span class="hljs-comment line-number">5.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">6.</span>| a    | b    |<br><span class="hljs-comment line-number">7.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">8.</span>|    8 | NULL |<br><span class="hljs-comment line-number">9.</span>|    8 |    9 |<br><span class="hljs-comment line-number">10.</span>|    8 | NULL |<br><span class="hljs-comment line-number">11.</span>|    8 |    9 |<br><span class="hljs-comment line-number">12.</span>|    8 | NULL |<br><span class="hljs-comment line-number">13.</span>|    8 |    9 |<br><span class="hljs-comment line-number">14.</span>|    8 | NULL |<br><span class="hljs-comment line-number">15.</span>|    8 |    9 |<br><span class="hljs-comment line-number">16.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">17.</span>8 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">18.</span><br><span class="hljs-comment line-number">19.</span>mysql&gt; <span class="hljs-keyword">delete</span> <span class="hljs-keyword">from</span> t3;</span>   <span class="hljs-comment">-- 删除整个表</span><br><span class="hljs-comment line-number">20.</span>Query OK, 8 rows affected (0.03 sec)<br><span class="hljs-comment line-number">21.</span><br><span class="hljs-comment line-number">22.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t3;</span><br><span class="hljs-comment line-number">23.</span>Empty <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<hr>

</div><div id="wmd-preview-section-24" class="wmd-preview-section preview-content">

<h2 id="四-update">四. UPDATE</h2>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/update.html" target="_blank">官方文档</a></p>
</blockquote>

</div><div id="wmd-preview-section-25" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t3 <span class="hljs-keyword">select</span> <span class="hljs-number">1</span>,<span class="hljs-number">2</span>;</span><br><span class="hljs-comment line-number">2.</span>Query OK, 1 row affected (0.03 sec)<br><span class="hljs-comment line-number">3.</span>Records: 1  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">4.</span><br><span class="hljs-comment line-number">5.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t3;</span><br><span class="hljs-comment line-number">6.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">7.</span>| a    | b    |<br><span class="hljs-comment line-number">8.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">9.</span>|    1 |    2 |<br><span class="hljs-comment line-number">10.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">11.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">12.</span><br><span class="hljs-comment line-number">13.</span>mysql&gt; <span class="hljs-keyword">update</span> t3 <span class="hljs-keyword">set</span> a=<span class="hljs-number">10</span> <span class="hljs-keyword">where</span> a=<span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">14.</span>Query OK, 1 row affected (0.03 sec)<br><span class="hljs-comment line-number">15.</span>Rows matched: 1  Changed: 1  Warnings: 0<br><span class="hljs-comment line-number">16.</span><br><span class="hljs-comment line-number">17.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t3;</span><br><span class="hljs-comment line-number">18.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">19.</span>| a    | b    |<br><span class="hljs-comment line-number">20.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">21.</span>|   10 |    2 |<br><span class="hljs-comment line-number">22.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">23.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">24.</span><br><span class="hljs-comment line-number">25.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">26.</span><span class="hljs-comment">-- 关联后更新</span><br><span class="hljs-comment line-number">27.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">28.</span>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t1;</span><br><span class="hljs-comment line-number">29.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">30.</span>| a    |<br><span class="hljs-comment line-number">31.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">32.</span>|   10 |<br><span class="hljs-comment line-number">33.</span>|    4 |  <span class="hljs-comment">-- 和t2中的4相等</span><br><span class="hljs-comment line-number">34.</span>|    1 |<br><span class="hljs-comment line-number">35.</span>|    2 |<br><span class="hljs-comment line-number">36.</span>|    3 |  <span class="hljs-comment">-- 和t2中的3相等</span><br><span class="hljs-comment line-number">37.</span>|   -1 |<br><span class="hljs-comment line-number">38.</span>|    8 |<br><span class="hljs-comment line-number">39.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">40.</span>7 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">41.</span><br><span class="hljs-comment line-number">42.</span>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t2;</span><br><span class="hljs-comment line-number">43.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">44.</span>| a    |<br><span class="hljs-comment line-number">45.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">46.</span>|    5 |<br><span class="hljs-comment line-number">47.</span>|    4 |  <span class="hljs-comment">-- 和t1中的4相等</span><br><span class="hljs-comment line-number">48.</span>|    3 |  <span class="hljs-comment">-- 和t1中的3相等</span><br><span class="hljs-comment line-number">49.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">50.</span>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">51.</span><br><span class="hljs-comment line-number">52.</span>mysql&gt; <span class="hljs-keyword">update</span> t1 <span class="hljs-keyword">join</span> t2 <span class="hljs-keyword">on</span> t1.a = t2.a <span class="hljs-keyword">set</span> t1.a=<span class="hljs-number">100</span>;</span>  <span class="hljs-comment">-- 先得到t1.a=t2.a的结果集</span><br><span class="hljs-comment line-number">53.</span>                                                       <span class="hljs-comment">-- 然后将结果集中的t1.a设置为100</span><br><span class="hljs-comment line-number">54.</span>Query OK, 2 rows affected (0.03 sec)<br><span class="hljs-comment line-number">55.</span>Rows matched: 2  Changed: 2  Warnings: 0<br><span class="hljs-comment line-number">56.</span><br><span class="hljs-comment line-number">57.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t1;</span><br><span class="hljs-comment line-number">58.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">59.</span>| a    |<br><span class="hljs-comment line-number">60.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">61.</span>|   10 |<br><span class="hljs-comment line-number">62.</span>|  100 |  <span class="hljs-comment">-- 该行被更新成100</span><br><span class="hljs-comment line-number">63.</span>|    1 |<br><span class="hljs-comment line-number">64.</span>|    2 |<br><span class="hljs-comment line-number">65.</span>|  100 |  <span class="hljs-comment">-- 该行被更新成100</span><br><span class="hljs-comment line-number">66.</span>|   -1 |<br><span class="hljs-comment line-number">67.</span>|    8 |<br><span class="hljs-comment line-number">68.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">69.</span>7 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<hr>

</div><div id="wmd-preview-section-26" class="wmd-preview-section preview-content">

<h2 id="五-replace">五. REPLACE</h2>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/replace.html" target="_blank">官方文档</a></p>
</blockquote>

</div><div id="wmd-preview-section-2217" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> t4(a <span class="hljs-built_in">int</span> primary <span class="hljs-keyword">key</span> auto_increment, b <span class="hljs-built_in">int</span>);</span><br><span class="hljs-comment line-number">2.</span>Query OK, 0 rows affected (0.15 sec)<br><span class="hljs-comment line-number">3.</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t4 <span class="hljs-keyword">values</span>(<span class="hljs-literal">NULL</span>, <span class="hljs-number">10</span>);</span><br><span class="hljs-comment line-number">5.</span>Query OK, 1 row affected (0.02 sec)<br><span class="hljs-comment line-number">6.</span><br><span class="hljs-comment line-number">7.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t4 <span class="hljs-keyword">values</span>(<span class="hljs-literal">NULL</span>, <span class="hljs-number">11</span>);</span><br><span class="hljs-comment line-number">8.</span>Query OK, 1 row affected (0.03 sec)<br><span class="hljs-comment line-number">9.</span><br><span class="hljs-comment line-number">10.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t4 <span class="hljs-keyword">values</span>(<span class="hljs-literal">NULL</span>, <span class="hljs-number">12</span>);</span><br><span class="hljs-comment line-number">11.</span>Query OK, 1 row affected (0.03 sec)<br><span class="hljs-comment line-number">12.</span><br><span class="hljs-comment line-number">13.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t4;</span><br><span class="hljs-comment line-number">14.</span>+<span class="hljs-comment">---+------+</span><br><span class="hljs-comment line-number">15.</span>| a | b    |<br><span class="hljs-comment line-number">16.</span>+<span class="hljs-comment">---+------+</span><br><span class="hljs-comment line-number">17.</span>| 1 |   10 |<br><span class="hljs-comment line-number">18.</span>| 2 |   11 |<br><span class="hljs-comment line-number">19.</span>| 3 |   12 |<br><span class="hljs-comment line-number">20.</span>+<span class="hljs-comment">---+------+</span><br><span class="hljs-comment line-number">21.</span>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">22.</span><br><span class="hljs-comment line-number">23.</span>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t4 <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>, <span class="hljs-number">100</span>);</span>  <span class="hljs-comment">-- 报错，说存在重复的主键记录 "1"</span><br><span class="hljs-comment line-number">24.</span>ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'<br><span class="hljs-comment line-number">25.</span><br><span class="hljs-comment line-number">26.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">replace</span> <span class="hljs-keyword">into</span> t4 <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>, <span class="hljs-number">100</span>);</span> <span class="hljs-comment">-- 替换该主键对应的值  </span><br><span class="hljs-comment line-number">27.</span>Query OK, 2 rows affected (0.03 sec)   <span class="hljs-comment">-- 两行记录受到影响</span><br><span class="hljs-comment line-number">28.</span><br><span class="hljs-comment line-number">29.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t4;</span><br><span class="hljs-comment line-number">30.</span>+<span class="hljs-comment">---+------+</span><br><span class="hljs-comment line-number">31.</span>| a | b    |<br><span class="hljs-comment line-number">32.</span>+<span class="hljs-comment">---+------+</span><br><span class="hljs-comment line-number">33.</span>| 1 |  100 |  <span class="hljs-comment">-- 已经被替换</span><br><span class="hljs-comment line-number">34.</span>| 2 |   11 |<br><span class="hljs-comment line-number">35.</span>| 3 |   12 |<br><span class="hljs-comment line-number">36.</span>+<span class="hljs-comment">---+------+</span><br><span class="hljs-comment line-number">37.</span>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">38.</span><span class="hljs-comment">-----</span><br><span class="hljs-comment line-number">39.</span><span class="hljs-comment">-- replace的原理是：先delete，在insert</span><br><span class="hljs-comment line-number">40.</span><span class="hljs-comment">-----</span><br><span class="hljs-comment line-number">41.</span><br><span class="hljs-comment line-number">42.</span>mysql&gt; <span class="hljs-keyword">replace</span> <span class="hljs-keyword">into</span> t4 <span class="hljs-keyword">values</span>(<span class="hljs-number">5</span>, <span class="hljs-number">50</span>);</span>  <span class="hljs-comment">-- 没有替换对象时，类似插入效果</span><br><span class="hljs-comment line-number">43.</span>Query OK, 1 row affected (0.03 sec)    <span class="hljs-comment">-- 只影响1行</span><br><span class="hljs-comment line-number">44.</span><br><span class="hljs-comment line-number">45.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t4;</span><br><span class="hljs-comment line-number">46.</span>+<span class="hljs-comment">---+------+</span><br><span class="hljs-comment line-number">47.</span>| a | b    |<br><span class="hljs-comment line-number">48.</span>+<span class="hljs-comment">---+------+</span><br><span class="hljs-comment line-number">49.</span>| 1 |  100 |<br><span class="hljs-comment line-number">50.</span>| 2 |   11 |<br><span class="hljs-comment line-number">51.</span>| 3 |   12 |<br><span class="hljs-comment line-number">52.</span>| 5 |   50 |  <span class="hljs-comment">-- 插入了1行</span><br><span class="hljs-comment line-number">53.</span>+<span class="hljs-comment">---+------+</span><br><span class="hljs-comment line-number">54.</span>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">55.</span><br><span class="hljs-comment line-number">56.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">57.</span><span class="hljs-comment">-- replace原理更明显的例子 </span><br><span class="hljs-comment line-number">58.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">59.</span><br><span class="hljs-comment line-number">60.</span>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> t6 <br><span class="hljs-comment line-number">61.</span>    -&gt; (a <span class="hljs-built_in">int</span> primary <span class="hljs-keyword">key</span>, <br><span class="hljs-comment line-number">62.</span>    -&gt; b <span class="hljs-built_in">int</span> auto_increment,   <span class="hljs-comment">-- b是auto_increment的int型数据</span><br><span class="hljs-comment line-number">63.</span>    -&gt; <span class="hljs-keyword">c</span> <span class="hljs-built_in">int</span>, <span class="hljs-keyword">key</span>(b));</span><br><span class="hljs-comment line-number">64.</span>Query OK, 0 rows affected (0.15 sec)<br><span class="hljs-comment line-number">65.</span><br><span class="hljs-comment line-number">66.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t6 <span class="hljs-keyword">values</span>(<span class="hljs-number">10</span>, <span class="hljs-literal">NULL</span>, <span class="hljs-number">100</span>),(<span class="hljs-number">20</span>,<span class="hljs-literal">NULL</span>,<span class="hljs-number">200</span>);</span>  <span class="hljs-comment">-- b自增长</span><br><span class="hljs-comment line-number">67.</span>Query OK, 2 rows affected (0.02 sec)<br><span class="hljs-comment line-number">68.</span>Records: 2  Duplicates: 0  Warnings: 0<br><span class="hljs-comment line-number">69.</span><br><span class="hljs-comment line-number">70.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t6;</span><br><span class="hljs-comment line-number">71.</span>+<span class="hljs-comment">----+---+------+</span><br><span class="hljs-comment line-number">72.</span>| a  | b | c    |<br><span class="hljs-comment line-number">73.</span>+<span class="hljs-comment">----+---+------+</span><br><span class="hljs-comment line-number">74.</span>| 10 | 1 |  100 |  <span class="hljs-comment">-- b为1</span><br><span class="hljs-comment line-number">75.</span>| 20 | 2 |  200 |  <span class="hljs-comment">-- b为2</span><br><span class="hljs-comment line-number">76.</span>+<span class="hljs-comment">----+---+------+</span><br><span class="hljs-comment line-number">77.</span>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">78.</span><br><span class="hljs-comment line-number">79.</span>mysql&gt; <span class="hljs-keyword">replace</span> <span class="hljs-keyword">into</span> t6 <span class="hljs-keyword">values</span>(<span class="hljs-number">10</span>,<span class="hljs-literal">NULL</span>,<span class="hljs-number">150</span>);</span>  <span class="hljs-comment">-- 将a=10的替换掉</span><br><span class="hljs-comment line-number">80.</span>Query OK, 2 rows affected (0.03 sec)<br><span class="hljs-comment line-number">81.</span><br><span class="hljs-comment line-number">82.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t6;</span><br><span class="hljs-comment line-number">83.</span>+<span class="hljs-comment">----+---+------+</span><br><span class="hljs-comment line-number">84.</span>| a  | b | c    |<br><span class="hljs-comment line-number">85.</span>+<span class="hljs-comment">----+---+------+</span><br><span class="hljs-comment line-number">86.</span>| 10 | 3 |  150 |  <span class="hljs-comment">-- 替换后b从1变成了3，说明是先删除，再插入</span><br><span class="hljs-comment line-number">87.</span>| 20 | 2 |  200 |<br><span class="hljs-comment line-number">88.</span>+<span class="hljs-comment">----+---+------+</span><br><span class="hljs-comment line-number">89.</span>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">90.</span><br><span class="hljs-comment line-number">91.</span><span class="hljs-comment">-----</span><br><span class="hljs-comment line-number">92.</span><br><span class="hljs-comment line-number">93.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">94.</span><span class="hljs-comment">-- insert on duplicate 效果和 replace类似</span><br><span class="hljs-comment line-number">95.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">96.</span>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t4 <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>,<span class="hljs-number">1</span>);</span>  <span class="hljs-comment">-- 插入报错，存在key为1的记录</span><br><span class="hljs-comment line-number">97.</span>ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'<br><span class="hljs-comment line-number">98.</span><br><span class="hljs-comment line-number">99.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t4 <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>,<span class="hljs-number">1</span>) <span class="hljs-keyword">on</span> <span class="hljs-keyword">duplicate</span> <span class="hljs-keyword">key</span> <span class="hljs-keyword">update</span> b=<span class="hljs-number">1</span>;</span>  <span class="hljs-comment">-- 带上on duplicate参数</span><br><span class="hljs-comment line-number">100.</span>                                                                <span class="hljs-comment">-- 非SQL标准，不推荐</span><br><span class="hljs-comment line-number">101.</span>Query OK, 2 rows affected (0.03 sec)<br><span class="hljs-comment line-number">102.</span><br><span class="hljs-comment line-number">103.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t4;</span><br><span class="hljs-comment line-number">104.</span>+<span class="hljs-comment">---+------+</span><br><span class="hljs-comment line-number">105.</span>| a | b    |<br><span class="hljs-comment line-number">106.</span>+<span class="hljs-comment">---+------+</span><br><span class="hljs-comment line-number">107.</span>| 1 |    1 |  <span class="hljs-comment">-- 该行的b列从100被替换成1</span><br><span class="hljs-comment line-number">108.</span>| 2 |   11 |<br><span class="hljs-comment line-number">109.</span>| 3 |   12 |<br><span class="hljs-comment line-number">110.</span>| 5 |   50 |<br><span class="hljs-comment line-number">111.</span>+<span class="hljs-comment">---+------+</span><br><span class="hljs-comment line-number">112.</span><br><span class="hljs-comment line-number">113.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">114.</span><span class="hljs-comment">-- insert ignore</span><br><span class="hljs-comment line-number">115.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">116.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">ignore</span> <span class="hljs-keyword">into</span> t4 <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>,<span class="hljs-number">1</span>);</span>   <span class="hljs-comment">-- 忽略重复的错误</span><br><span class="hljs-comment line-number">117.</span>Query OK, 0 rows affected, 1 warning (0.00 sec)<br><span class="hljs-comment line-number">118.</span><br><span class="hljs-comment line-number">119.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">warnings</span>;</span><br><span class="hljs-comment line-number">120.</span>+<span class="hljs-comment">---------+------+---------------------------------------+</span><br><span class="hljs-comment line-number">121.</span>| Level   | Code | Message                               |<br><span class="hljs-comment line-number">122.</span>+<span class="hljs-comment">---------+------+---------------------------------------+</span><br><span class="hljs-comment line-number">123.</span>| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |<br><span class="hljs-comment line-number">124.</span>+<span class="hljs-comment">---------+------+---------------------------------------+</span><br><span class="hljs-comment line-number">125.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<hr></div><div id="wmd-preview-section-28" class="wmd-preview-section preview-content">

<h2 id="六-其他知识点">六. 其他知识点</h2>

<ul><li><strong>更新有关系的值</strong></li>
</ul>

</div><div id="wmd-preview-section-29" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> t5 (a <span class="hljs-built_in">int</span>, b <span class="hljs-built_in">int</span>);</span><br><span class="hljs-comment line-number">2.</span>Query OK, 0 rows affected (0.14 sec)<br><span class="hljs-comment line-number">3.</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> t5 <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>,<span class="hljs-number">1</span>);</span><br><span class="hljs-comment line-number">5.</span>Query OK, 1 row affected (0.03 sec)<br><span class="hljs-comment line-number">6.</span><br><span class="hljs-comment line-number">7.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t5;</span><br><span class="hljs-comment line-number">8.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">9.</span>| a    | b    |<br><span class="hljs-comment line-number">10.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">11.</span>|    1 |    1 |<br><span class="hljs-comment line-number">12.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">13.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">14.</span><br><span class="hljs-comment line-number">15.</span>mysql&gt; <span class="hljs-keyword">update</span> t5 <span class="hljs-keyword">set</span> a=a+<span class="hljs-number">1</span>, b=a <span class="hljs-keyword">where</span> a=<span class="hljs-number">1</span>;</span><br><span class="hljs-comment line-number">16.</span>Query OK, 1 row affected (0.02 sec)<br><span class="hljs-comment line-number">17.</span>Rows matched: 1  Changed: 1  Warnings: 0<br><span class="hljs-comment line-number">18.</span><br><span class="hljs-comment line-number">19.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> t5;</span><br><span class="hljs-comment line-number">20.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">21.</span>| a    | b    |<br><span class="hljs-comment line-number">22.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">23.</span>|    2 |    2 |  <span class="hljs-comment">-- SQL Server和Oracle中得到的值是2, 1</span><br><span class="hljs-comment line-number">24.</span>+<span class="hljs-comment">------+------+</span><br><span class="hljs-comment line-number">25.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> se</span><br></code></pre>

<ul><li><strong>显示行号(RowNumber)</strong></li>
</ul>

</div><div id="wmd-preview-section-30" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment line-number">1.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">2.</span><span class="hljs-comment">-- 方法一</span><br><span class="hljs-comment line-number">3.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">4.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">use</span> employees ;</span><br><span class="hljs-comment line-number">5.</span>Reading table information for completion of table and column names<br><span class="hljs-comment line-number">6.</span>You can turn off this feature to get a quicker startup with -A<br><span class="hljs-comment line-number">7.</span><br><span class="hljs-comment line-number">8.</span>Database changed<br><span class="hljs-comment line-number">9.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">set</span> @rn:=<span class="hljs-number">0</span>;</span>  <span class="hljs-comment">-- 产生 SESSION(会话)级别的变量</span><br><span class="hljs-comment line-number">10.</span>Query OK, 0 rows affected (0.00 sec)<br><span class="hljs-comment line-number">11.</span><br><span class="hljs-comment line-number">12.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> @rn:=@rn+<span class="hljs-number">1</span> <span class="hljs-keyword">as</span> rownumber, emp_no, gender <span class="hljs-keyword">from</span> employees <span class="hljs-keyword">limit</span> <span class="hljs-number">10</span>;</span>  <span class="hljs-comment">-- ：= 是赋值的意思</span><br><span class="hljs-comment line-number">13.</span>+<span class="hljs-comment">-----------+--------+--------+</span><br><span class="hljs-comment line-number">14.</span>| rownumber | emp_no | gender |<br><span class="hljs-comment line-number">15.</span>+<span class="hljs-comment">-----------+--------+--------+</span><br><span class="hljs-comment line-number">16.</span>|        11 |  10001 | M      |<br><span class="hljs-comment line-number">17.</span>|        12 |  10002 | F      |<br><span class="hljs-comment line-number">18.</span>|        13 |  10003 | M      |<br><span class="hljs-comment line-number">19.</span>|        14 |  10004 | M      |<br><span class="hljs-comment line-number">20.</span>|        15 |  10005 | M      |<br><span class="hljs-comment line-number">21.</span>|        16 |  10006 | F      |<br><span class="hljs-comment line-number">22.</span>|        17 |  10007 | F      |<br><span class="hljs-comment line-number">23.</span>|        18 |  10008 | M      |<br><span class="hljs-comment line-number">24.</span>|        19 |  10009 | F      |<br><span class="hljs-comment line-number">25.</span>|        20 |  10010 | F      |<br><span class="hljs-comment line-number">26.</span>+<span class="hljs-comment">-----------+--------+--------+</span><br><span class="hljs-comment line-number">27.</span>10 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">28.</span><br><span class="hljs-comment line-number">29.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">30.</span><span class="hljs-comment">-- 方法二 （推荐）</span><br><span class="hljs-comment line-number">31.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">32.</span>mysql&gt; <span class="hljs-keyword">select</span> @rn1:=@rn1+<span class="hljs-number">1</span> <span class="hljs-keyword">as</span> rownumber, emp_no, gender <span class="hljs-keyword">from</span> employees, (<span class="hljs-keyword">select</span> @rn1:=<span class="hljs-number">0</span>) <span class="hljs-keyword">as</span> a <span class="hljs-keyword">limit</span> <span class="hljs-number">10</span>;</span><br><span class="hljs-comment line-number">33.</span>+<span class="hljs-comment">-----------+--------+--------+</span><br><span class="hljs-comment line-number">34.</span>| rownumber | emp_no | gender |<br><span class="hljs-comment line-number">35.</span>+<span class="hljs-comment">-----------+--------+--------+</span><br><span class="hljs-comment line-number">36.</span>|         1 |  10001 | M      |<br><span class="hljs-comment line-number">37.</span>|         2 |  10002 | F      |<br><span class="hljs-comment line-number">38.</span>|         3 |  10003 | M      |<br><span class="hljs-comment line-number">39.</span>|         4 |  10004 | M      |<br><span class="hljs-comment line-number">40.</span>|         5 |  10005 | M      |<br><span class="hljs-comment line-number">41.</span>|         6 |  10006 | F      |<br><span class="hljs-comment line-number">42.</span>|         7 |  10007 | F      |<br><span class="hljs-comment line-number">43.</span>|         8 |  10008 | M      |<br><span class="hljs-comment line-number">44.</span>|         9 |  10009 | F      |<br><span class="hljs-comment line-number">45.</span>|        10 |  10010 | F      |<br><span class="hljs-comment line-number">46.</span>+<span class="hljs-comment">-----------+--------+--------+</span><br><span class="hljs-comment line-number">47.</span>10 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">48.</span><br><span class="hljs-comment line-number">49.</span><span class="hljs-comment">-- MySQL 自定义变量，根据每一记录进行变化的</span><br><span class="hljs-comment line-number">50.</span><br><span class="hljs-comment line-number">51.</span>mysql&gt; <span class="hljs-keyword">select</span> @rn1:=<span class="hljs-number">0</span>;</span><br><span class="hljs-comment line-number">52.</span>+<span class="hljs-comment">---------+</span><br><span class="hljs-comment line-number">53.</span>| @rn1:=0 |<br><span class="hljs-comment line-number">54.</span>+<span class="hljs-comment">---------+</span><br><span class="hljs-comment line-number">55.</span>|       0 |  <span class="hljs-comment">-- 只有一行记录</span><br><span class="hljs-comment line-number">56.</span>+<span class="hljs-comment">---------+</span><br><span class="hljs-comment line-number">57.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">58.</span><br><span class="hljs-comment line-number">59.</span><span class="hljs-comment">-- 相当于 把 employees 和 (select @rn1:=0)做了笛卡尔积，然后使用@rn1:=@rn + 1，根据每行进行累加</span><br><span class="hljs-comment line-number">60.</span><br><span class="hljs-comment line-number">61.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">62.</span><span class="hljs-comment">-- ":=" 和 "="</span><br><span class="hljs-comment line-number">63.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">64.</span>mysql&gt; <span class="hljs-keyword">set</span> @a:=<span class="hljs-number">1</span>;</span>  <span class="hljs-comment">-- 赋值为1</span><br><span class="hljs-comment line-number">65.</span>Query OK, 0 rows affected (0.00 sec)<br><span class="hljs-comment line-number">66.</span><br><span class="hljs-comment line-number">67.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> @a;</span><br><span class="hljs-comment line-number">68.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">69.</span>| @a   |<br><span class="hljs-comment line-number">70.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">71.</span>|    1 |<br><span class="hljs-comment line-number">72.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">73.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">74.</span><br><span class="hljs-comment line-number">75.</span>mysql&gt; <span class="hljs-keyword">set</span> @a:=<span class="hljs-number">10</span>;</span>  <span class="hljs-comment">-- 赋值为10</span><br><span class="hljs-comment line-number">76.</span>Query OK, 0 rows affected (0.00 sec)<br><span class="hljs-comment line-number">77.</span><br><span class="hljs-comment line-number">78.</span>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> @a;</span><br><span class="hljs-comment line-number">79.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">80.</span>| @a   |<br><span class="hljs-comment line-number">81.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">82.</span>|   10 |<br><span class="hljs-comment line-number">83.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">84.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">85.</span><br><span class="hljs-comment line-number">86.</span>mysql&gt; <span class="hljs-keyword">select</span> @a=<span class="hljs-number">9</span>;</span>  <span class="hljs-comment">-- 进行比较</span><br><span class="hljs-comment line-number">87.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">88.</span>| @a=9 |<br><span class="hljs-comment line-number">89.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">90.</span>|    0 |  <span class="hljs-comment">-- 返回为False</span><br><span class="hljs-comment line-number">91.</span>+<span class="hljs-comment">------+</span><br><span class="hljs-comment line-number">92.</span>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment line-number">93.</span><br><span class="hljs-comment line-number">94.</span><span class="hljs-comment">--</span><br><span class="hljs-comment line-number">95.</span><span class="hljs-comment">-- 作业：通过子查询或者其他方式，计算出employees的行号</span><br><span class="hljs-comment line-number">96.</span><span class="hljs-comment">--</span></span><br></code></pre></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>